package cn.edu.swu;

import cn.edu.swu.picture.Picture;
import org.junit.Test;

import javax.xml.transform.Result;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JDBCtest {

    List<Picture> pictures = new ArrayList<>();
    //连接数据库
    public Connection getConnection() throws SQLException {
        //jdbc协议下连接mysql,地址为本机，再接上数据库名字
        String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/landscapepictures";
        String dbUser  = "root";
        String dbPass  = "159357";
        Connection connection = DriverManager.getConnection(jdbcUrl,dbUser,dbPass);
        return connection;
    }
    //Java中单元测试喜欢用junit测试框架，要下载jar包，maven repository
    @Test
    public void selectTest() throws SQLException {
        //建立数据库的连接
       try(Connection connection = this.getConnection()){
           //创建一个访问数据库的陈述
           try(Statement statement = connection.createStatement()){
               //查询
               String sql = "SELECT * FROM picture";
               ResultSet rs = statement.executeQuery(sql);
               //用while循环逐条取出每条数据
               while(rs.next()){
                   Picture pi = new Picture();
                   pi.setId(rs.getInt("id"));
                   pi.setType(rs.getString("Type"));
                   pi.setDOL(rs.getString("DOL"));
                   pi.setMemo(rs.getString("memo"));
                   pi.setStore(rs.getDate("store"));

                   //每个picture都依次添加进去
                   pictures.add(pi);
               }
           }
       }

       //打印出所有图片
       pictures.forEach(e -> System.out.println(e.toString()));
    }

    @Test
    public void insertTest() throws SQLException{
        try(Connection connection = this.getConnection()){
            try(Statement statement = connection.createStatement()){
                String sql = "INSERT INTO picture (Type, DOL, memo, store)" + "value ('%s', '%s', '%s', '%s')";
                //这里不再是询问了，而是要更改数据,所以用execute
                statement.execute(String.format(sql,"River and flowers","Very love","The river flows along the flowers","2024-12-7"));
            }
        }
    }



    @Test
    public void deleteTest() throws SQLException{
        try(Connection connection = this.getConnection()){
            try(Statement statement = connection.createStatement()){
                String sql = "DELETE FROM picture WHERE id = %s";
                statement.execute(String.format(sql,2));
            }
        }
    }

    @Test
    public void updateTest() throws SQLException{
        try(Connection connection = this.getConnection()){
            try(Statement statement = connection.createStatement()){
                String sql = "UPDATE picture SET type = '%s' WHERE id = %s";
                //这里execute只能用于更改type
                statement.execute(String.format(sql,"River", 2));
            }
        }
    }

    @Test
    public void alterTest() throws SQLException{
        try(Connection connection = this.getConnection()){
            try(Statement statement = connection.createStatement()){
                String sql = "ALTER TABLE picture ADD COLUMN image VARCHAR(50)";
                statement.execute(sql);
            }
        }
    }
}
